﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections.Generic;
using EC2011_hk1_BT6_1041326_1041337_1041373_1041448.DTO;
using EC2011_hk1_BT6_1041326_1041337_1041373_1041448.Utils;

namespace EC2011_hk1_BT6_1041326_1041337_1041373_1041448.DAO
{
    public class EmployeeDAO
    {
        #region Select
        /// <summary>
        /// Lấy tất cả các nhân viên.
        /// </summary>
        /// <returns></returns>
        public static List<EmployeeDTO> GetAll()
        {
            List<EmployeeDTO> list = new List<EmployeeDTO>();
            String sql = String.Format(
                    "SELECT * FROM [{0}], [{1}] WHERE [{0}].[DepartmentID]=[{1}].[DepartmentID] ", "Employees",
                    "Departments");
            DataSet ds = DBConnector.ExecuteQuery(sql);
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                EmployeeDTO dto = new EmployeeDTO();
                dto.EmployeeID = int.Parse(row["EmployeeID"].ToString());
                dto.LastName = row["LastName"].ToString();
                dto.FirstName = row["FirstName"].ToString();
                dto.Department.DepartmentID = int.Parse(row["DepartmentID"].ToString());
                dto.Department.Name = row["Name"].ToString();
                dto.Department.UrlPhoto = row["URLPhoto"].ToString();
                dto.Department.UrlHomePage = row["URLHomePage"].ToString();
                dto.Department.ShowControl = row["ShowControl"].ToString();
                dto.Salary = Decimal.Parse(row["Salary"].ToString());
                dto.HireDate = DateTime.Parse(row["HireDate"].ToString());
                dto.UrlPersonalPhoto = row["URLPersonalPhoto"].ToString();
                dto.Description = row["Description"].ToString();
                list.Add(dto);
            }
            return list;
        }

        /// <summary>
        /// Lấy tất cả các nhân viên theo một phòng ban nào đó.
        /// </summary>
        /// <param name="departmentID">ID của phòng ban.</param>
        /// <returns></returns>
        public static List<EmployeeDTO> GetAllByDepartment(int departmentID)
        {
            List<EmployeeDTO> list = new List<EmployeeDTO>();
            String sql = String.Format(
                    "SELECT * FROM [{0}], [{1}] WHERE [{0}].[DepartmentID]=[{1}].[DepartmentID] AND [{1}].[DepartmentID]={2} ", "Employees", "Departments", departmentID);
            DataSet ds = DBConnector.ExecuteQuery(sql);
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                EmployeeDTO dto = new EmployeeDTO();
                dto.EmployeeID = int.Parse(row["EmployeeID"].ToString());
                dto.LastName = row["LastName"].ToString();
                dto.FirstName = row["FirstName"].ToString();
                dto.Department.DepartmentID = int.Parse(row["DepartmentID"].ToString());
                dto.Department.Name = row["Name"].ToString();
                dto.Department.UrlPhoto = row["URLPhoto"].ToString();
                dto.Department.UrlHomePage = row["URLHomePage"].ToString();
                dto.Department.ShowControl = row["ShowControl"].ToString();
                dto.Salary = Decimal.Parse(row["Salary"].ToString());
                dto.HireDate = DateTime.Parse(row["HireDate"].ToString());
                dto.UrlPersonalPhoto = row["URLPersonalPhoto"].ToString();
                dto.Description = row["Description"].ToString();
                list.Add(dto);
            }
            return list;
        }

        /// <summary>
        /// Lấy ra một nhân viên.
        /// </summary>
        /// <param name="id">ID của nhân viên</param>
        /// <returns></returns>
        public static EmployeeDTO GetEmployee(int id)
        {
            EmployeeDTO dto = new EmployeeDTO();
            String sql = String.Format(
                    "SELECT * FROM [{0}], [{1}] WHERE [{0}].[DepartmentID]=[{1}].[DepartmentID] AND [{0}].[EmployeeID]={2} ", "Employees", "Departments", id);
            DataSet ds = DBConnector.ExecuteQuery(sql);
            if (ds.Tables.Count != 0 && ds.Tables["Departments"].Rows.Count != 0)
            {
                DataRow row = ds.Tables[0].Rows[0];
                dto.EmployeeID = int.Parse(row["EmployeeID"].ToString());
                dto.LastName = row["LastName"].ToString();
                dto.FirstName = row["FirstName"].ToString();
                dto.Department.DepartmentID = int.Parse(row["DepartmentID"].ToString());
                dto.Department.Name = row["Name"].ToString();
                dto.Department.UrlPhoto = row["URLPhoto"].ToString();
                dto.Department.UrlHomePage = row["URLHomePage"].ToString();
                dto.Department.ShowControl = row["ShowControl"].ToString();
                dto.Salary = Decimal.Parse(row["Salary"].ToString());
                dto.HireDate = DateTime.Parse(row["HireDate"].ToString());
                dto.UrlPersonalPhoto = row["URLPersonalPhoto"].ToString();
                dto.Description = row["Description"].ToString();
            }
            return dto;
        }
        #endregion

        #region Insert
        /// <summary>
        /// Thêm một nhân viên.
        /// </summary>
        /// <param name="lastName">Họ.</param>
        /// <param name="firstName">Tên.</param>
        /// <param name="departmentID">ID của phòng ban.</param>
        /// <param name="salary">Mức lương.</param>
        /// <param name="hireDate">Ngày vào làm.</param>
        /// <param name="urlPersonalPhoto">Đường dẫn hình cá nhân.</param>
        /// <param name="description">Mô tả/Ghi chú.</param>
        /// <returns></returns>
        public static bool Insert(String lastName, String firstName, int departmentID, Decimal salary, DateTime hireDate, String urlPersonalPhoto, String description)
        {
            String sql = String.Format("INSERT INTO [{0}]([LastName], [FirstName], [DepartmentID], [Salary], [HireDate], [URLPersonalPhoto], [Description]) ", "Employees");
            sql += String.Format(" VALUES('{0}', '{1}', {2}, {3}, '{4}', '{5}', '{6}')", lastName, firstName, departmentID, salary, hireDate, urlPersonalPhoto, description);
            return (DBConnector.ExecuteNonQuery(sql) == 1);
        }
        #endregion

        #region Update
        /// <summary>
        /// Cập nhật nhân viên.
        /// </summary>
        /// <param name="lastName">Họ.</param>
        /// <param name="firstName">Tên.</param>
        /// <param name="departmentID">ID của phòng ban.</param>
        /// <param name="salary">Mức lương.</param>
        /// <param name="hireDate">Ngày vào làm.</param>
        /// <param name="urlPersonalPhoto">Đường dẫn hình cá nhân.</param>
        /// <param name="description">Mô tả/Ghi chú.</param>
        /// <param name="id">ID của nhân viên.</param>
        /// <returns></returns>
        public static bool Update(String lastName, String firstName, int departmentID, Decimal salary, DateTime hireDate, String urlPersonalPhoto, String description, int id)
        {
            String sql = String.Format(" UPDATE [{0}] SET ", "Employees");
            sql += String.Format(" [LastName]='{0}', ", lastName);
            sql += String.Format(" [FirstName]='{0}', ", firstName);
            sql += String.Format(" [DepartmentID]={0}, ", departmentID);
            sql += String.Format(" [Salary]={0}, ", salary);
            sql += String.Format(" [HireDate]='{0}', ", hireDate);
            sql += String.Format(" [URLPersonalPhoto]='{0}', ", urlPersonalPhoto);
            sql += String.Format(" [Description]='{0}', ", description);
            sql += String.Format(" WHERE [EmployeeID]={0}", id);
            return (DBConnector.ExecuteNonQuery(sql) == 1);
        }
        #endregion

        #region Delete
        /// <summary>
        /// Xóa nhân viên.
        /// </summary>
        /// <param name="ids">Mảng ID của nhân viên.</param>
        /// <returns>Số nhân viên có thể xóa được.</returns>
        public static int Delete(int[] ids)
        {
            if (ids.Length == 0)
                return 0;
            String sql = String.Format(" DELETE FROM [{0}] WHERE ", "Employees");
            for (int i = 0; i < ids.Length - 1; i++)
                sql += String.Format(" [EmployeeID]={0} OR ", ids[i]);
            sql += String.Format(" [EmployeeID]={0} ", ids[ids.Length - 1]);
            return (DBConnector.ExecuteNonQuery(sql));
        }
        #endregion
    }
}
